﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.ComponentModel;
using System.Configuration;

namespace EarringsRecommendationWeb
{
    public static class SqlClientUtility
    {
        //private static string connectionStringName = "Server=(local);Database=Earrings;UID=sa;PWD=1234;";
        //private static string connectionStringName = "Server=192.168.1.112;Database=PMSDB;UID=sa;PWD=tostos;";
        private static string connectionStringName = ConfigurationManager.ConnectionStrings["ConnStringDb"].ToString();       

        private static object CheckValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            return value;
        }

        private static SqlCommand CreateCommand(SqlConnection connection, CommandType commandType, string commandText)
        {
            if ((connection != null) && (connection.State == ConnectionState.Closed))
            {
                connection.Open();
            }
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = commandText;
            command.CommandType = commandType;
            return command;
        }

        private static SqlCommand CreateCommand(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            if ((connection != null) && (connection.State == ConnectionState.Closed))
            {
                connection.Open();
            }
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = commandText;
            command.CommandType = commandType;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }
        //@New
        private static SqlCommand CreateCommand(SqlTransaction trans, CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            SqlConnection connection = trans.Connection;

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.Transaction = trans;
            command.CommandText = commandText;
            command.CommandType = commandType;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        private static DataSet CreateDataSet(SqlCommand command)
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                return dataSet;
            }
        }

        private static DataTable CreateDataTable(SqlCommand command)
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);
                return dataTable;
            }
        }

        public static DataSet ExecuteDataSet(CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            SqlCommand command;
            //if (Transaction.Current == null)
            //{
            using (SqlConnection connection = new SqlConnection(connectionStringName))
            {
                using (command = CreateCommand(connection, commandType, commandText, parameters))
                {
                    return CreateDataSet(command);
                }
            }
            //}
            //using (command = CreateCommand(GetTransactionSqlConnection(connectionStringName), commandType, commandText, parameters))
            //{
            //    return CreateDataSet(command);
            //}
        }

        public static DataTable ExecuteDataTable(CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(connectionStringName))
            {
                using (command = CreateCommand(connection, commandType, commandText, parameters))
                {
                    return CreateDataTable(command);
                }
            }
        }
        //@New
        public static DataRow ExecuteDataRow(CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(connectionStringName))
            {
                using (command = CreateCommand(connection, commandType, commandText, parameters))
                {
                    return GetDataRow(CreateDataTable(command));
                }
            }
        }
        //@Edit
        public static void ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            SqlCommand command;
            SqlConnection connection = null;
            SqlTransaction trans = null;
            try
            {
                using (connection = new SqlConnection(connectionStringName))
                {
                    if ((connection != null) && (connection.State == ConnectionState.Closed))
                    {
                        connection.Open();
                    }
                    trans = connection.BeginTransaction();
                    using (command = CreateCommand(trans, commandType, commandText, parameters))
                    {
                        command.ExecuteNonQuery();
                    }
                    trans.Commit();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    if (trans != null)
                    {
                        trans.Rollback();
                    }
                    connection.Close();
                }

                throw new Exception(ex.Message);
            }
        }

        //@New
        public static void ExecuteNonQuery(SqlTransaction trans, CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            SqlCommand command;

            using (command = CreateCommand(trans, commandType, commandText, parameters))
            {
                command.ExecuteNonQuery();
            }
            return;
        }

        public static SqlDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            SqlCommand command;
            //if (Transaction.Current == null)
            //{
            SqlConnection connection = new SqlConnection(connectionStringName);
            using (command = CreateCommand(connection, commandType, commandText, parameters))
            {
                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            //}
            //using (command = CreateCommand(GetTransactionSqlConnection(connectionStringName), commandType, commandText, parameters))
            //{
            //    return command.ExecuteReader();
            //}
        }
        //@New
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            SqlCommand command;

            using (command = CreateCommand(connection, commandType, commandText, parameters))
            {
                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }

        public static object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] parameters)
        {
            SqlCommand command;
            //if (Transaction.Current == null)
            //{
            SqlConnection connection = new SqlConnection(connectionStringName);
            using (command = CreateCommand(connection, commandType, commandText, parameters))
            {
                return command.ExecuteScalar();
            }
            //}
            //using (command = CreateCommand(GetTransactionSqlConnection(connectionStringName), commandType, commandText, parameters))
            //{
            //    return command.ExecuteScalar();
            //}
        }

        #region "  Get Value  "
        public static bool GetBoolean(DataRow dataRow, string columnName, bool valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is bool)
            {
                return (bool)obj2;
            }
            if (!(obj2 is byte))
            {
                return bool.Parse(obj2.ToString());
            }
            if (((byte)obj2) == 0)
            {
                return false;
            }
            return true;
        }

        public static bool GetBoolean(SqlDataReader dataReader, string columnName, bool valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, valueIfNull);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is bool)
            {
                return (bool)obj2;
            }
            if (!(obj2 is byte))
            {
                return bool.Parse(obj2.ToString());
            }
            if (((byte)obj2) == 0)
            {
                return false;
            }
            return true;
        }

        public static byte GetByte(DataRow dataRow, string columnName, byte valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is byte)
            {
                return (byte)obj2;
            }
            return byte.Parse(obj2.ToString());
        }

        public static byte GetByte(SqlDataReader dataReader, string columnName, byte valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is byte)
            {
                return (byte)obj2;
            }
            return byte.Parse(obj2.ToString());
        }

        public static byte[] GetBytes(DataRow dataRow, string columnName, byte[] valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if ((obj2 != null) && (obj2 is byte[]))
            {
                return (byte[])obj2;
            }
            return valueIfNull;
        }

        public static byte[] GetBytes(SqlDataReader dataReader, string columnName, byte[] valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, null);
            if ((obj2 != null) && (obj2 is byte[]))
            {
                return (byte[])obj2;
            }
            return valueIfNull;
        }

        public static DataRow GetDataRow(DataSet dataSet)
        {
            return GetDataRow(GetDataTable(dataSet));
        }

        public static DataRow GetDataRow(DataTable dataTable)
        {
            if ((dataTable != null) && (dataTable.Rows.Count == 1))
            {
                return dataTable.Rows[0];
            }
            return null;
        }

        public static DataTable GetDataTable(DataSet dataSet)
        {
            if ((dataSet != null) && (dataSet.Tables.Count == 1))
            {
                return dataSet.Tables[0];
            }
            return null;
        }

        public static DateTime GetDateTime(DataRow dataRow, string columnName, DateTime valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is DateTime)
            {
                return (DateTime)obj2;
            }
            return DateTime.Parse(obj2.ToString());
        }

        public static DateTime GetDateTime(SqlDataReader dataReader, string columnName, DateTime valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is DateTime)
            {
                return (DateTime)obj2;
            }
            return DateTime.Parse(obj2.ToString());
        }

        public static decimal GetDecimal(DataRow dataRow, string columnName, decimal valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is decimal)
            {
                return (decimal)obj2;
            }
            return decimal.Parse(obj2.ToString());
        }

        public static decimal GetDecimal(SqlDataReader dataReader, string columnName, decimal valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is decimal)
            {
                return (decimal)obj2;
            }
            return decimal.Parse(obj2.ToString());
        }

        public static double GetDouble(DataRow dataRow, string columnName, double valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is double)
            {
                return (double)obj2;
            }
            return double.Parse(obj2.ToString());
        }

        public static double GetDouble(SqlDataReader dataReader, string columnName, double valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is double)
            {
                return (double)obj2;
            }
            return double.Parse(obj2.ToString());
        }

        public static float GetFloat(DataRow dataRow, string columnName, float valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is float)
            {
                return (float)obj2;
            }
            return float.Parse(obj2.ToString());
        }

        public static float GetFloat(SqlDataReader dataReader, string columnName, float valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is float)
            {
                return (float)obj2;
            }
            return float.Parse(obj2.ToString());
        }

        public static Guid GetGuid(DataRow dataRow, string columnName, Guid valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is Guid)
            {
                return (Guid)obj2;
            }
            return new Guid(obj2.ToString());
        }

        public static Guid GetGuid(SqlDataReader dataReader, string columnName, Guid valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is Guid)
            {
                return (Guid)obj2;
            }
            return new Guid(obj2.ToString());
        }

        public static short GetInt16(DataRow dataRow, string columnName, short valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is short)
            {
                return (short)obj2;
            }
            return short.Parse(obj2.ToString());
        }

        public static short GetInt16(SqlDataReader dataReader, string columnName, short valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is short)
            {
                return (short)obj2;
            }
            return short.Parse(obj2.ToString());
        }

        public static int GetInt32(DataRow dataRow, string columnName, int valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is int)
            {
                return (int)obj2;
            }
            return int.Parse(obj2.ToString());
        }

        public static int GetInt32(SqlDataReader dataReader, string columnName, int valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is int)
            {
                return (int)obj2;
            }
            return int.Parse(obj2.ToString());
        }

        public static long GetInt64(DataRow dataRow, string columnName, long valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is long)
            {
                return (long)obj2;
            }
            return long.Parse(obj2.ToString());
        }

        public static long GetInt64(SqlDataReader dataReader, string columnName, long valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is long)
            {
                return (long)obj2;
            }
            return long.Parse(obj2.ToString());
        }

        public static object GetObject(DataRow dataRow, string columnName, object valueIfNull)
        {
            object obj2 = dataRow[columnName];
            if ((obj2 != null) && (obj2 != DBNull.Value))
            {
                return obj2;
            }
            return valueIfNull;
        }

        public static object GetObject(SqlDataReader dataReader, string columnName, object valueIfNull)
        {
            object obj2 = dataReader[columnName];
            if ((obj2 != null) && (obj2 != DBNull.Value))
            {
                return obj2;
            }
            return valueIfNull;
        }

        public static float GetSingle(DataRow dataRow, string columnName, float valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is float)
            {
                return (float)obj2;
            }
            return float.Parse(obj2.ToString());
        }

        public static float GetSingle(SqlDataReader dataReader, string columnName, float valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is float)
            {
                return (float)obj2;
            }
            return float.Parse(obj2.ToString());
        }

        public static string GetString(DataRow dataRow, string columnName, string valueIfNull)
        {
            object obj2 = GetObject(dataRow, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is string)
            {
                return (string)obj2;
            }
            return obj2.ToString();
        }

        public static string GetString(SqlDataReader dataReader, string columnName, string valueIfNull)
        {
            object obj2 = GetObject(dataReader, columnName, null);
            if (obj2 == null)
            {
                return valueIfNull;
            }
            if (obj2 is string)
            {
                return (string)obj2;
            }
            return obj2.ToString();
        }
        #endregion

        public static bool IsForeignKeyContraintException(Exception e)
        {
            SqlException exception = e as SqlException;
            return ((exception != null) && (exception.Number == 0x223));
        }

        public static bool IsUniqueConstraintException(Exception e)
        {
            SqlException exception = e as SqlException;
            if ((exception == null) || ((exception.Number != 0xa43) && (exception.Number != 0xa29)))
            {
                return false;
            }
            return true;
        }
        //@New
        public static bool TestConnection(string _connectionStringName, string _commandText)
        {
            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(_connectionStringName))
            {
                using (command = new SqlCommand(_commandText, connection))
                {
                    return CreateDataTable(command).Rows.Count > -1;
                }
            }
        }
        //@New
        public static int GetRunningNo(SqlTransaction trans, string subject, string[] args)
        {
            SqlCommand cmd = new SqlCommand("[SP_UTIL_GetRunningNo]", trans.Connection);
            cmd.Transaction = trans;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Result", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@Subject", SqlDbType.NVarChar).Value = subject;

            if ((args != null))
            {
                for (int i = 0; i <= args.Length - 1; i++)
                {
                    cmd.Parameters.Add("@Arg" + i, SqlDbType.NVarChar).Value = args[i];
                }
            }


            cmd.ExecuteNonQuery();

            int ret;
            Int32.TryParse(cmd.Parameters["@Result"].Value.ToString(), out ret);

            return ret;
        }

        public static T MakeModel<T>(SqlDataReader dataReader) where T : class
        {
            Type classType = typeof(T);
            T Model = Activator.CreateInstance(classType) as T;

            PropertyInfo[] lstProp = classType.GetProperties();

            foreach (PropertyInfo pi in lstProp)
            {
                if (pi.Name.Substring(0, 1) == "_")
                {
                    continue;
                }

                System.ComponentModel.AttributeCollection attributes = TypeDescriptor.GetProperties(classType)[pi.Name].Attributes;
                CategoryAttribute myAttribute = (CategoryAttribute)attributes[typeof(CategoryAttribute)];

                string type = pi.PropertyType.FullName.Replace("System.", "");

                switch (type)
                {
                    case "Boolean": pi.SetValue(Model, SqlClientUtility.GetBoolean(dataReader, pi.Name, false), null);
                        break;
                    case "String": pi.SetValue(Model, SqlClientUtility.GetString(dataReader, pi.Name, string.Empty), null);
                        break;
                    case "Int16": pi.SetValue(Model, SqlClientUtility.GetInt16(dataReader, pi.Name, 0), null);
                        break;
                    case "Int32": pi.SetValue(Model, SqlClientUtility.GetInt32(dataReader, pi.Name, 0), null);
                        break;
                    case "Int64": pi.SetValue(Model, SqlClientUtility.GetInt64(dataReader, pi.Name, 0), null);
                        break;
                    case "Byte": pi.SetValue(Model, SqlClientUtility.GetByte(dataReader, pi.Name, 0), null);
                        break;
                    case "DateTime": pi.SetValue(Model, SqlClientUtility.GetDateTime(dataReader, pi.Name, DateTime.MinValue), null);
                        break;
                    case "Decimal": pi.SetValue(Model, SqlClientUtility.GetDecimal(dataReader, pi.Name, Decimal.MinValue), null);
                        break;
                    case "Double": pi.SetValue(Model, SqlClientUtility.GetDouble(dataReader, pi.Name, Double.MinValue), null);
                        break;
                    case "Single": pi.SetValue(Model, SqlClientUtility.GetFloat(dataReader, pi.Name, Single.MinValue), null);
                        break;
                    case "Guid": pi.SetValue(Model, SqlClientUtility.GetGuid(dataReader, pi.Name, Guid.Empty), null);
                        break;
                    case "Object": pi.SetValue(Model, SqlClientUtility.GetObject(dataReader, pi.Name, null), null);
                        break;
                }
            }

            return Model;
        }
    }
}